package vn.lumi.smarthome.io.database;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import vn.lumi.smarthome.Config;
import vn.lumi.smarthome.ulti.LogUltis;
import android.annotation.SuppressLint;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHelper extends SQLiteOpenHelper {
	private static final String TAG = DatabaseHelper.class.getName();

	public static final String DB_NAME = "smarthome";
	/**
	 * Version cua database
	 */
	public static final int DATABASE_VERSION = 1;

	public static final String DB_TBL_DEVICE = "Device";
	public static final String DB_TBL_DEVICE_SCENE = "DeviceScene";
	public static final String DB_TBL_DEVICE_STATE = "DeviceState";
	public static final String DB_TBL_FLOOR = "Floor";
	public static final String DB_TBL_ROOM = "Room";
	public static final String DB_TBL_SCENE = "Scene";
	public static final String DB_TBL_SCHEDULE = "Schedule";
	public static final String DB_TBL_USER = "User";

	private static DatabaseHelper mDatabaseHelper = null;

	public static DatabaseHelper getInstance(Context context) {
		if (mDatabaseHelper == null) {
			mDatabaseHelper = new DatabaseHelper(context, DB_NAME, null, DATABASE_VERSION);
		}
		return mDatabaseHelper;
	}
	
	private static SQLiteDatabase mSQLiteDBDream=null;
	
	@SuppressLint("SdCardPath")
	public static SQLiteDatabase getInstanceDatabaseDream(Context context){
		String mPathDB = "/data/data/" + context.getPackageName() + "/databases/"+Config.DATABASE_NAME;
		if(mSQLiteDBDream==null)
			mSQLiteDBDream=SQLiteDatabase.openDatabase(mPathDB, null, SQLiteDatabase.OPEN_READWRITE);
		return mSQLiteDBDream;
	}

	private static final StringBuilder DB_CREATE_TBL_DEVICE = new StringBuilder();
	private static final StringBuilder DB_CREATE_TBL_DEVICE_SCENE = new StringBuilder();
	private static final StringBuilder DB_CREATE_TBL_DEVICE_STATE = new StringBuilder();
	private static final StringBuilder DB_CREATE_TBL_FLOOR = new StringBuilder();
	private static final StringBuilder DB_CREATE_TBL_ROOM = new StringBuilder();
	private static final StringBuilder DB_CREATE_TBL_SCENE = new StringBuilder();
	private static final StringBuilder DB_CREATE_TBL_SCHEDULE = new StringBuilder();
	private static final StringBuilder DB_CREATE_TBL_USER = new StringBuilder();

	static {
		// tao bang Device
		DB_CREATE_TBL_DEVICE.append("create table ");
		DB_CREATE_TBL_DEVICE.append(DB_TBL_DEVICE);
		DB_CREATE_TBL_DEVICE.append(" (");
		DB_CREATE_TBL_DEVICE.append(TBL_Device._ID
				+ " integer primary key autoincrement");
		DB_CREATE_TBL_DEVICE.append(",");
		DB_CREATE_TBL_DEVICE.append(TBL_Device.TYPE_ID + " integer");
		DB_CREATE_TBL_DEVICE.append(",");
		DB_CREATE_TBL_DEVICE.append(TBL_Device.ROOM_ID + " integer");
		DB_CREATE_TBL_DEVICE.append(",");
		DB_CREATE_TBL_DEVICE.append(TBL_Device.NAME + " text");
		DB_CREATE_TBL_DEVICE.append(",");
		DB_CREATE_TBL_DEVICE.append(TBL_Device.STATE_ID + " integer");
		DB_CREATE_TBL_DEVICE.append(",");
		DB_CREATE_TBL_DEVICE.append(TBL_Device.IS_CONTROL + " integer");
		DB_CREATE_TBL_DEVICE.append(",");
		DB_CREATE_TBL_DEVICE.append(TBL_Device.NUM_ORDER + " integer");
		DB_CREATE_TBL_DEVICE.append(")");

		// tao bang DeviceScene
		DB_CREATE_TBL_DEVICE_SCENE.append("create table ");
		DB_CREATE_TBL_DEVICE_SCENE.append(DB_TBL_DEVICE_SCENE);
		DB_CREATE_TBL_DEVICE_SCENE.append(" (");
		DB_CREATE_TBL_DEVICE_SCENE.append(TBL_DeviceScene._ID
				+ " integer primary key autoincrement");
		DB_CREATE_TBL_DEVICE_SCENE.append(",");
		DB_CREATE_TBL_DEVICE_SCENE.append(TBL_DeviceScene.SCENE_ID + " integer");
		DB_CREATE_TBL_DEVICE_SCENE.append(",");
		DB_CREATE_TBL_DEVICE_SCENE.append(TBL_DeviceScene.DEVICE_ID + " integer");
		DB_CREATE_TBL_DEVICE_SCENE.append(",");
		DB_CREATE_TBL_DEVICE_SCENE.append(TBL_DeviceScene.STATE_ID + " integer");
		DB_CREATE_TBL_DEVICE_SCENE.append(")");
		
		//tao bang DeviceState
		DB_CREATE_TBL_DEVICE_STATE.append("create table ");
		DB_CREATE_TBL_DEVICE_STATE.append(DB_TBL_DEVICE_STATE);
		DB_CREATE_TBL_DEVICE_STATE.append(" (");
		DB_CREATE_TBL_DEVICE_STATE.append(TBL_DeviceState._ID
				+ " integer primary key autoincrement");
		DB_CREATE_TBL_DEVICE_STATE.append(",");
		DB_CREATE_TBL_DEVICE_STATE.append(TBL_DeviceState.BLIND_STATE + " integer");
		DB_CREATE_TBL_DEVICE_STATE.append(",");		
		DB_CREATE_TBL_DEVICE_STATE.append(TBL_DeviceState.VALUE + " integer");
		DB_CREATE_TBL_DEVICE_STATE.append(",");
		DB_CREATE_TBL_DEVICE_STATE.append(TBL_DeviceState.RGB + " integer");
		DB_CREATE_TBL_DEVICE_STATE.append(")");
		
		//tao bang Floor
		DB_CREATE_TBL_FLOOR.append("create table ");
		DB_CREATE_TBL_FLOOR.append(DB_TBL_FLOOR);
		DB_CREATE_TBL_FLOOR.append(" (");
		DB_CREATE_TBL_FLOOR.append(TBL_Floor._ID
				+ " integer primary key autoincrement");
		DB_CREATE_TBL_FLOOR.append(",");
		DB_CREATE_TBL_FLOOR.append(TBL_Floor.NAME + " text");
		DB_CREATE_TBL_FLOOR.append(")");
		
		//tao bang Room
		DB_CREATE_TBL_ROOM.append("create table ");
		DB_CREATE_TBL_ROOM.append(DB_TBL_ROOM);
		DB_CREATE_TBL_ROOM.append(" (");
		DB_CREATE_TBL_ROOM.append(TBL_Room._ID
				+ " integer primary key autoincrement");
		DB_CREATE_TBL_ROOM.append(",");
		DB_CREATE_TBL_ROOM.append(TBL_Room.FLOOR_ID + " integer");
		DB_CREATE_TBL_ROOM.append(",");
		DB_CREATE_TBL_ROOM.append(TBL_Room.NAME + " text");
		DB_CREATE_TBL_ROOM.append(")");
		
		//tao bang Scene
		DB_CREATE_TBL_SCENE.append("create table ");
		DB_CREATE_TBL_SCENE.append(DB_TBL_SCENE);
		DB_CREATE_TBL_SCENE.append(" (");
		DB_CREATE_TBL_SCENE.append(TBL_Scene._ID
				+ " integer primary key autoincrement");
		DB_CREATE_TBL_SCENE.append(",");
		DB_CREATE_TBL_SCENE.append(TBL_Scene.ROOM_ID + " integer");
		DB_CREATE_TBL_SCENE.append(",");
		DB_CREATE_TBL_SCENE.append(TBL_Scene.NAME + " text");
		DB_CREATE_TBL_SCENE.append(",");
		DB_CREATE_TBL_SCENE.append(TBL_Scene.STATE + " integer");
		DB_CREATE_TBL_SCENE.append(",");
		DB_CREATE_TBL_SCENE.append(TBL_Scene.MODE + " integer");
		DB_CREATE_TBL_SCENE.append(",");
		DB_CREATE_TBL_SCENE.append(TBL_Scene.HOUR + " integer");
		DB_CREATE_TBL_SCENE.append(",");
		DB_CREATE_TBL_SCENE.append(TBL_Scene.MINUTE + " integer");
		DB_CREATE_TBL_SCENE.append(")");
		
		
		//tao bang Schedule
		DB_CREATE_TBL_SCHEDULE.append("create table ");
		DB_CREATE_TBL_SCHEDULE.append(DB_TBL_SCHEDULE);
		DB_CREATE_TBL_SCHEDULE.append(" (");
		DB_CREATE_TBL_SCHEDULE.append(TBL_Schedule._ID
				+ " integer primary key autoincrement");
		DB_CREATE_TBL_SCHEDULE.append(",");
		DB_CREATE_TBL_SCHEDULE.append(TBL_Schedule.DEVICE_ID + " integer");
		DB_CREATE_TBL_SCHEDULE.append(",");
		DB_CREATE_TBL_SCHEDULE.append(TBL_Schedule.STATE + " integer");
		DB_CREATE_TBL_SCHEDULE.append(",");
		DB_CREATE_TBL_SCHEDULE.append(TBL_Schedule.REPEAT + " integer");
		DB_CREATE_TBL_SCHEDULE.append(",");
		DB_CREATE_TBL_SCHEDULE.append(TBL_Schedule.MONDAY + " integer");
		DB_CREATE_TBL_SCHEDULE.append(",");
		DB_CREATE_TBL_SCHEDULE.append(TBL_Schedule.TUESDAY + " integer");
		DB_CREATE_TBL_SCHEDULE.append(",");
		DB_CREATE_TBL_SCHEDULE.append(TBL_Schedule.WEDNESDAY + " integer");
		DB_CREATE_TBL_SCHEDULE.append(",");
		DB_CREATE_TBL_SCHEDULE.append(TBL_Schedule.THURSDAY + " integer");
		DB_CREATE_TBL_SCHEDULE.append(",");
		DB_CREATE_TBL_SCHEDULE.append(TBL_Schedule.FRIDAY+ " integer");
		DB_CREATE_TBL_SCHEDULE.append(",");
		DB_CREATE_TBL_SCHEDULE.append(TBL_Schedule.SATURDAY + " integer");
		DB_CREATE_TBL_SCHEDULE.append(",");
		DB_CREATE_TBL_SCHEDULE.append(TBL_Schedule.SUNDAY + " integer");
		DB_CREATE_TBL_SCHEDULE.append(",");
		DB_CREATE_TBL_SCHEDULE.append(TBL_Schedule.HOUR + " integer");
		DB_CREATE_TBL_SCHEDULE.append(",");
		DB_CREATE_TBL_SCHEDULE.append(TBL_Schedule.MINUTE + " integer");
		DB_CREATE_TBL_SCHEDULE.append(",");
		DB_CREATE_TBL_SCHEDULE.append(TBL_Schedule.HOUR_END + " integer");
		DB_CREATE_TBL_SCHEDULE.append(",");
		DB_CREATE_TBL_SCHEDULE.append(TBL_Schedule.MINUTE_END + " integer");
		DB_CREATE_TBL_SCHEDULE.append(",");
		DB_CREATE_TBL_SCHEDULE.append(TBL_Schedule.STATE_ID + " integer");
		DB_CREATE_TBL_SCHEDULE.append(")");
		
		//tao bang User
		DB_CREATE_TBL_USER.append("create table ");
		DB_CREATE_TBL_USER.append(DB_TBL_USER);
		DB_CREATE_TBL_USER.append(" (");
		DB_CREATE_TBL_USER.append(TBL_User._ID
				+ " integer primary key autoincrement");
		DB_CREATE_TBL_USER.append(",");
		DB_CREATE_TBL_USER.append(TBL_User.UID + " text");
		DB_CREATE_TBL_USER.append(",");
		DB_CREATE_TBL_USER.append(TBL_User.PWD + " text");
		DB_CREATE_TBL_USER.append(",");
		DB_CREATE_TBL_USER.append(TBL_User.AUTO_LOGIN + " integer");
		DB_CREATE_TBL_USER.append(",");
		DB_CREATE_TBL_USER.append(TBL_User.CONFIG_VERSION + " integer");
		DB_CREATE_TBL_USER.append(",");
		DB_CREATE_TBL_USER.append(TBL_User.LAN_IP + " integer");
		DB_CREATE_TBL_USER.append(",");
		DB_CREATE_TBL_USER.append(TBL_User.DNS_SERVER + " integer");
		DB_CREATE_TBL_USER.append(")");
	}

	private DatabaseHelper(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(DB_CREATE_TBL_FLOOR.toString());
		db.execSQL(DB_CREATE_TBL_ROOM.toString());		
		db.execSQL(DB_CREATE_TBL_DEVICE.toString());
		db.execSQL(DB_CREATE_TBL_SCENE.toString());
		db.execSQL(DB_CREATE_TBL_DEVICE_SCENE.toString());
		db.execSQL(DB_CREATE_TBL_SCHEDULE.toString());
		db.execSQL(DB_CREATE_TBL_DEVICE_STATE.toString());
		db.execSQL(DB_CREATE_TBL_USER.toString());
		LogUltis.i(TAG, "create database");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		if(oldVersion==newVersion) return;
		db.execSQL("DROP TABLE IF EXISTS " + DB_TBL_DEVICE);
		db.execSQL("DROP TABLE IF EXISTS " + DB_TBL_DEVICE_SCENE);
		db.execSQL("DROP TABLE IF EXISTS " + DB_TBL_DEVICE_STATE);
		db.execSQL("DROP TABLE IF EXISTS " + DB_TBL_FLOOR);
		db.execSQL("DROP TABLE IF EXISTS " + DB_TBL_ROOM);
		db.execSQL("DROP TABLE IF EXISTS " + DB_TBL_SCENE);
		db.execSQL("DROP TABLE IF EXISTS " + DB_TBL_SCHEDULE);
		db.execSQL("DROP TABLE IF EXISTS " + DB_TBL_USER);
		onCreate(db);
		
		LogUltis.i(TAG, "upgrade db");
	}
	
	
	@SuppressLint("SdCardPath")
	public static boolean copyDataBase(Context context,String name)
    {
	
		String pathDB = "/data/data/" + context.getPackageName() + "/databases/"+Config.DATABASE_NAME;
		String pathFolder_DB = "/data/data/" + context.getPackageName() + "/databases";
		
		File f = new File(pathDB);
		if (!f.exists())
		{
		    File f2 = new File(pathFolder_DB);
		    f2.mkdir();
		    try
		    {
			InputStream is = context.getAssets().open(name);
		    OutputStream os = new FileOutputStream(new File(pathDB));
			copyStream(is,os);
			return true;
		    }
		    catch (IOException e)
		    {
		    	e.printStackTrace();
		    	return false;
		    }
		}
		return false;
    }
    
    private static boolean copyStream(InputStream is,OutputStream os)
    {
	try
	{
	    byte[] b = new byte[1024];
	    int length;
	    LogUltis.d(TAG,"Bat dau copy");
	    while ((length = is.read(b)) != -1)
	    {
		os.write(b, 0, length);
	    }
	    os.close();
	    is.close();
	    LogUltis.d(TAG,"Copy xong");
	    return true;
	}
	catch (IOException e)
	{
	    e.printStackTrace();
	    return false;
	}
    }

	
}
